package com.example.mobileeducationstudentv;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.telephony.SmsManager;
import android.util.Base64;
import android.util.Log;

public class FileDataStorage extends SQLiteOpenHelper {

	// All Static variables
	// Database Version
	private static final int DATABASE_VERSION = 15;

	// Database Name
	private static final String DATABASE_NAME = "study_materials";

	// Files and Quizzes table name
	private static final String TABLE_FILES = "files_opened";
	private static final String TABLE_QUIZZES = "quizzes_taken";

	// Files Table and Quizzes Table Columns names
	private static final String KEY_ID = "id";
	private static final String KEY_PATH = "path";
	private static final String KEY_SCORE = "score";

	public FileDataStorage(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	// Creating Tables
	@Override
	public void onCreate(SQLiteDatabase db) {
		String CREATE_FILES_TABLE = "CREATE TABLE " + TABLE_FILES + "("
				+ KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT ," + KEY_PATH
				+ " TEXT" + ")";
		db.execSQL(CREATE_FILES_TABLE);
		String CREATE_QUIZ_TABLE = "CREATE TABLE " + TABLE_QUIZZES + "("
				+ KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT ," + KEY_PATH
				+ " TEXT, " + KEY_SCORE + " TEXT" + ")";
		db.execSQL(CREATE_QUIZ_TABLE);
	}

	// Upgrading database
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// Drop older table if existed
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_FILES);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_QUIZZES);
		onCreate(db);
	}

	// check database if file is read
	public boolean isFileRead(String path) {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor c = db.rawQuery("SELECT * FROM " + TABLE_FILES + " WHERE "
				+ KEY_PATH + " = '" + path + "'", null);
		int count = c.getCount();
		c.close();
		return (count > 0);
	}

	// create recreate files table (only called when table is dropped)
	public void recreateTable(SQLiteDatabase db) {
		String CREATE_FILES_TABLE = "CREATE TABLE " + TABLE_FILES + "("
				+ KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT ," + KEY_PATH
				+ " TEXT" + ")";
		db.execSQL(CREATE_FILES_TABLE);
	}

	// insert newly read file into database
	public void readFile(String path) {
		if (!isFileRead(path)) {
			SQLiteDatabase db = this.getWritableDatabase();
			db.execSQL("INSERT INTO " + TABLE_FILES + " (" + KEY_PATH
					+ ") VALUES ('" + path + "')");
			db.close();
		}
	}

	// check if quiz is taken, retval = -1 if false
	public int isQuizTaken(String path) {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor c = db.rawQuery("SELECT * FROM " + TABLE_QUIZZES + " WHERE "
				+ KEY_PATH + " = '" + path + "'", null);
		int count = c.getCount();
		int retval = -1;
		if (count > 0) {
			c.moveToFirst();
			retval = Integer.valueOf(c.getString(c.getColumnIndex(KEY_SCORE)));
		}
		c.close();
		return retval;
	}

	// insert newly taken assignment/quiz into database with score and send text
	// message
	public void takeQuiz(String path, int score, String id) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.execSQL("INSERT INTO " + TABLE_QUIZZES + " (" + KEY_PATH + ","
				+ KEY_SCORE + ") VALUES ('" + path + "','"
				+ String.valueOf(score) + "')");
		db.close();
		// send text
		try {
			String phoneNo = "5556";
			String sms = Base64.encodeToString(
					(id + ";" + String.valueOf(score)).getBytes("UTF-8"),
					Base64.DEFAULT);
			SmsManager smsManager = SmsManager.getDefault();
			smsManager.sendTextMessage(phoneNo, null, "Assignment;" + sms,
					null, null);
			Log.i("Success", "Message sent sucessfully! " + sms);
		} catch (Exception e) {
			Log.e("Failure", "Critical error, message sending failed");
			e.printStackTrace();
		}
	}

	// drop database table for files
	public void clearFiles() {
		SQLiteDatabase db = this.getWritableDatabase();
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_FILES);
		recreateTable(db);
	}

}
